"""
FIVEDASH(TM) -- General Purpose Accounting Software
Copyright (C) 2008  Obol Software Pty Ltd

This program is free software: you can redistribute it and/or modify
it. For terms and conditions, see fivedash/license/license.txt

"""

import datetime
from mod_python import apache
from mod_python import Session
from mod_python import util

from phunc import *
from phunc.cx_wrapper import db_tools
from phunc.logger import logger

# legacy namespacing
GeneralBusinessErr = exception.GeneralBusinessErr
genErrorPage, showErr = error.genErrorPage, error.showErr
jtable , sljtable = table.jtable, table.sljtable

def check_special_params(cursor):
	"""
	#TODO: put a comment here
	Returns a tuple of 2 lists
	"""
	special_params = [{'param_id':'special_ac_cash','param_name':'CASH','param_desc':'CASH'},{'param_id':'special_ac_equip','param_name':'Capital Equipment','param_desc':'Capital Equipment'},{'param_id':'special_ac_comm_pay','param_name':'Commissions Payable','param_desc':'Commissions Payable'},{'param_id':'special_ac_sales_rev','param_name':'Sales Revenue','param_desc':'Sales Revenue'},{'param_id':'special_ac_comm_exp','param_name':'Commissions','param_desc':'Commissions'}]
	leave_params = []
	set_params = []
	for param in special_params:
		exist_special_params = None
		if param['param_desc']:
			exist_special_params = db_tools.exec_action(cursor, 'check_special_params', [param['param_id'],param['param_desc']])
		else:
			exist_special_params = db_tools.exec_action(cursor, 'check_special_params2', [param['param_id']])
		if exist_special_params:
			set_params.append(param)
		else:
			leave_params.append(param)	
	return set_params,leave_params

def index( req, **params ):
	"""displays the GL account list with balances and links"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	accounts = db_tools.exec_action(dbc,'get_accounts2')
	accounts_type = db_tools.exec_action(dbc,'get_accounts_type')
	slink_list=[]
	for account in accounts:
		slink_dict={}
		if account['subsidiary_shortname'] is None:
			slink = ''
		else:
			slink = '<a href="/bin/finance.py/sub_account_index?sl=%s%s">detail</a>' % (account['subsidiary_shortname'], info.query_string.replace('?', '&amp;'))
		slink_dict['id']=account['id']
		slink_dict['slink']=slink
		slink_list.append(slink_dict)
	page.content = {
			'accounts':accounts,
			'info_query_string':info.query_string.replace('?','&amp;'),
			'slink_list':slink_list,
			'accounts_type':accounts_type,
			}                
	page.setup = {
		'breadcrumbs':['Finance'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account','special','linked', 'subledger', 'spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Chart of Accounts',
		'broadcast_message':broadcast_message,
		'template':'finance/f.opt',
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def sub_account_index( req,**params ):
	"""displays a subledger account list with balances and links"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	accounts = db_tools.exec_action(dbc,'get_accounts',[params['sl'],params['sl']])
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]                
	ledger = control['name']
	page.content = {
		'accounts':accounts,
		'info_sl':params['sl'],
		'info_query_string':info.query_string.replace('?','&amp;'),
		'ledger':ledger
	}
	page.setup = {
		'breadcrumbs':['finance', ledger],
		'menu_title':'<dl class="ledger"><dd title="'+ledger+'">' + ledger + '</dd><dt> Ledger</dt></dl>',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal','editsubjournal','postsubjournal'],
		'content_title':'Chart of Accounts',
		'broadcast_message':broadcast_message,
		'template':'finance/f-sl.opt'
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl']
	}
	return page.render(debug=True)

def insert_sub_account1( req,**params ):
	"""display form 1 of 2 for creating an account in the current subledger"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	link_candidates = db_tools.exec_action(dbc,'get_link_candidates')
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]                
	ledger = control['name']
	page.content = {
		'link_candidates':link_candidates,
		'info_sl':params['sl']
	}
	page.setup = {
		'breadcrumbs':['finance', 'subledger', 'New Account'],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount!','spacer','subjournal','editsubjournal','postsubjournal'],
		'content_title':'Create Account',
		'template':'finance/fac1-sl.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl'],
	}
	return page.render()

def insert_sub_account2( req,**params ):
	"""display form 1 of 2 for creating an account in the current subledger"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]                
	ledger = control['name']
	params_list={}
	for field in params.keys():
		params_list[field]=params[field]
	if params.has_key('link_candidate') and params['link_candidate']!='':
		page.content = {
			'submit':False,
			'link_candidate':params['link_candidate'].capitalize(),
			'link_objects':db_tools.exec_action(dbc,'get_link_objects',[params['link_candidate']]),
			'params_name':params.keys(),
			'params_list':params
		}
	else:   
		page.content = {
			'submit':True,
			'params_name':params.keys(),
			'params_list':params
		}
	page.setup = {
		'breadcrumbs':['finance', 'subledger', 'New Account'],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount!','spacer','subjournal','editsubjournal','postsubjournal'],
		'content_title':'Create Account',
		'template':'finance/fac2-sl.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl'],
	}
	return page.render()

def create_account( req,**params ):
	"""actually creates a GL account, redirects to finance index page (GL)"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()           
		var_map={'p_ledger':'','p_id':'','p_name':'','p_account_type_id':'','p_subsidiary_shortname':''}
		account_name = params['name']
		account_id = params['id']
		account_type = params['account_type_id']
		var_map['p_id'] = account_id
		var_map['p_name'] = account_name
		var_map['p_account_type_id'] = account_type
		if params.has_key('has_subledger'): 
			subledger_name = params['short_name']
			import re
			p=re.compile(r'[a-zA-Z]+[a-zA-Z0-9_]*[a-zA-Z0-9]')
			v=p.match(subledger_name)
			if not v:
				broadcast_message="Subledger shortname must start with a letter and contain only alphanumeric characters"
				util.redirect(req,'/bin/finance.py?broadcast_message=%s'%broadcast_message)
			else:                                               
				var_map['p_subsidiary_shortname'] = subledger_name
		result = db_tools.exec_action(dbc,'create_account',[var_map['p_ledger'],var_map['p_id'],var_map['p_name'],var_map['p_account_type_id'],var_map['p_subsidiary_shortname']])[0]['create_account']
		conn.commit()
		result_desc = showErr(int(result))
		broadcast_message = result_desc
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e:
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py?broadcast_message=%s'%broadcast_message)

def create_sub_account(req,**params):
	"""actually creates a subledger account -- redirects to subledger"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()            
		broadcast_message = ''                
		result = 'null'
		if params.has_key('name') and params['name']!='':
			same_view = db_tools.exec_action(dbc,'get_sub_account_name',[params['sl'],params['name']])
			if len(same_view)==0:
				result = db_tools.exec_action(dbc,'create_account',[params['sl'],'',params['name'],'',''])[0]['create_account']   
		else:
			same_view = db_tools.exec_action(dbc,'get_sub_account_name',[params['sl'],'#'+params['link_candidate']+':'+params['link_object_id']])
			if len(same_view)==0: 
				   result = db_tools.exec_action(dbc,'create_account',[params['sl'],'','#'+params['link_candidate']+':'+params['link_object_id'],'',''])[0]['create_account']
		conn.commit()
		result_desc = showErr(int(result))
		broadcast_message = result_desc
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/finance.py/sub_account_index?broadcast_message=%s&amp;sl=%s"%(broadcast_message,params['sl']))

def insert_account(req,**params):
	"""displays a form for createing a GL account"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	page.content = {
		'actypes':db_tools.exec_action(dbc,'get_account_type')
	}
	page.setup = {
		'breadcrumbs':['finance','Create an Account'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account!', 'special','linked','subledger', 'spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Create an Account',
		'template':'finance/fac.opt',
		'form':True
		}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def show_account_details(req,**params):
	"""display t-account view of a GL account"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	account=''
	if params.has_key('sl') and params['sl']:
		account = db_tools.exec_action(dbc,'get_account2',[params['sl'],params['a']])
	if len(account)==0:
		account = db_tools.exec_action(dbc,'get_account',[params['a']])
	if account:
		account=account[0]['name']
	page.content = {
		'no_render':table.atable(dbc, 'general_' + params['a'], info.query_string)
	}
	page.setup = {
		'window_title'	:'A' + params['a'],
		'breadcrumbs':['finance',params['a']],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account','special','linked', 'subledger','spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':account,
		'template':'no_render.opt',
	}
	page.info = {
		'query_string':info.query_string,
		'a':params['a']
	}
	return page.render()

def show_sub_account_details(req,**params):
	"""displays t-account view of a subledger account"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	account_name = db_tools.exec_action(dbc,'get_account2',[params['sl'],params['a']])[0]['name']
	if account_name.find(':',0)>=0 and account_name.find('#',0)>=0:                        
		account_name = db_tools.exec_action(dbc,'get_table_name',[account_name])[0]['find_name']
	control_account_id = db_tools.exec_action(dbc,'get_control_account_id',[params['sl']])[0]['id']
	account_id = '0000' + params['a']
	formatted_account_id = str(control_account_id) + '.' + account_id[-5:]
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]                
	ledger = control['name']               
	page.content = {
		'no_render':table.atable(dbc, params['sl'] + '_' + params['a'], info.query_string)
	}
	page.setup = {
		'window_title':'A' + formatted_account_id,
		'breadcrumbs':['finance','subledger',formatted_account_id],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal','editsubjournal','postsubjournal'],
		'content_title':account_name,
		'template':'no_render.opt'
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl'],
		'a':params['a']
	}
	return page.render()

def insert_journal(req,**params):
	"""displays a form for creating a GL journal"""
	page = Page(req, company_db=True)
	page.content = {
		'date_time':datetime.date.today().strftime('%d %b %Y'),
		'operator_name':page.session['login_user_name'],
		'coa':db_tools.exec_action(page.company_db.cursor(),'get_coa'),
		'skin':page.session['skin']
	}
	page.setup = {
		'breadcrumbs':['finance','Write a Journal'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked','subledger', 'spacer', 'journal!', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Write a Journal',
		'template':'finance/fjc.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def insert_sub_journal(req,**params):
	"""displays a form for creating a subledger journal"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	d_coa = db_tools.exec_action(dbc,'get_scoa',[params['sl']])
	for ac in d_coa:
		ac['sid'] = int(ac['id'].split('.')[1])
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]                
	ledger = control['name']
	page.content = {
		'd_coa':d_coa,
		'g_coa':db_tools.exec_action(dbc,'get_coa'),
		'datetime_time':datetime.date.today().strftime('%d %b %Y'),
		'info_sl':params['sl'],
		'g_account_id':control['id'],
		'operator_name':page.session['login_user_name'],
		'skin':page.session['skin']
	}
	page.setup = {
		'breadcrumbs':['finance','subledger','New Journal'],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal!','editsubjournal','postsubjournal'],
		'content_title':'Create Journal',
		'template':'finance/fjc-sl.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl']
	}
	return page.render()

def list_journal(req,**params):
	"""displays a list of completed GL journals, with some links"""
	page = Page(req, company_db=True)
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	journals = db_tools.exec_action(page.company_db.cursor(),'get_journals2')
	for journal in journals:
		journal['label'] = '<a href="/bin/finance.py/edit_journal?j=%s%s">%s\' \'%s</a>'%(str(journal['id']),info.query_string.replace('?', '&amp;'),str(journal['id']),str(journal['date']))
	form_setup={
		'id':'journals',
		'action':'/bin/finance.py/delete_journal',
		'fieldsets':[{
			'id':None,
			'classid':'form fin-label',
			'legend':'Completed Journals',
			'items':[  
				{'type':'checkbox','checkbox_type':2,'checkbox_value':journals,'whole_label':None,'label':'label','id':'id','name':'journalId','value':'id','format':4},                                        
			]
		}],                      
		'template_dir':info.getTemplateDir(req),
		'form_bottom': {'type':'div','id':None,'classid':'bottom input','style':None,'items':[{'type':'submit','id':'submit','name':'submit','value':'Delete selected'}]},
	}
	page.content = {
		'journals':journals,
		'info_query_string':info.query_string.replace('?', '&amp;'),
		'uform':form.user_form(**form_setup)
	}
	page.setup = {
		'breadcrumbs':['finance','Edit or Delete Journals'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked','subledger', 'spacer', 'journal', 'editjournal!', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Edit or Delete Journals',
		'broadcast_message':broadcast_message,
		'template':'finance/fjd.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def list_sub_journal(req, **params):
	"""displays a list of completed subledger journals, with some links"""
	page = Page(req, company_db=True)
	dbc=page.company_db.cursor()	
	ledger = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]['name']     
	page.content = {
		'journals':db_tools.exec_action(page.company_db.cursor(),'get_journals',['=\'%s\''%params['sl']]),
		'info_query_string':info.query_string.replace('?', '&amp;'),
		'info_sl':params['sl']
	}
	page.setup = {
		'breadcrumbs':['finance','subledger','Journals'],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal','editsubjournal!','postsubjournal'],
		'content_title':'Edit or Delete Unposted Journals',
		'template':'finance/fjd-sl.opt',
		'form':True,
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or '',
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl'],
	}
	return page.render()

def edit_journal(req,**params):
	"""displays a form for editing an unposted journal"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	page.content = {
		'coa':db_tools.exec_action(dbc,'get_coa'),
		'info_j':params['j'],
		'date':db_tools.exec_action(dbc,'get_journal2',[params['j']])[0]['date'],
		'journals':db_tools.exec_action(dbc,'get_journals2'),
		'operator_name':page.session['login_user_name'],
		'journal_items':db_tools.exec_action(dbc, 'get_journal_items', [int(params['j'])]),
		'skin':page.session['skin']
	}
	page.setup = {
		'breadcrumbs':['finance','journals','Edit J' + params['j']],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked','subledger', 'spacer', 'journal', 'editjournal!', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Edit Journal ' + params['j'],
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or '',
		'template':'finance/fje.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string,
		'j':params['j']
	}
	return page.render()

def edit_sub_journal(req,**params):
	"""displays a form for editing an unposted journal"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	d_coa = db_tools.exec_action(dbc,'get_scoa',[params['sl']])                
	for ac in d_coa:
		ac['sid'] = int(ac['id'].split('.')[1])
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]                
	ledger = control['name']                
	journal_items = db_tools.exec_action(dbc, 'get_journal_items', [int(params['j'])])
	page.content = {
		'd_coa':d_coa,
		'g_coa':db_tools.exec_action(dbc,'get_coa'),
		'g_account_id':control['id'],
		'operator_name':page.session['login_user_name'],
		'info_j':params['j'],
		'info_sl':params['sl'],
		'datetime_time':db_tools.exec_action(dbc,'get_journal2',[params['j']])[0]['date'],
		'journals':db_tools.exec_action(dbc,'get_journals',['=\'%s\''%params['sl']]),
		'sub_journal_items':db_tools.exec_action(dbc, 'get_sub_journal_items', [params['sl'],params['j']]),
		'journal_items':journal_items,
		'has_input_DR':'false',
		'has_input_CR':'false',
		'skin':page.session['skin']
	}
	for item in journal_items:
		if item['account_id'] == control['id']:
			if item['drcr']=='DR':
				page.content['has_input_DR'] = 'true'
			else:
				page.content['has_input_CR'] = 'true'
			break            
	page.setup = {
		'breadcrumbs':['finance','subledger','subjournals','Edit J' + params['j']],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal','editsubjournal!','postsubjournal'],
		'content_title':'Edit Journal ' + params['j'],
		'broadcast_message':broadcast_message,
		'template':'finance/fje-sl.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl'],
	}
	return page.render()

def create_sub_journal(req, **params):
	"""actually creates a subledger journal -- redirects to post subledger journals page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()               
		var_map={'p_description':'','p_account_id':'','p_amount':'','p_drcr':'','p_subledger':'','p_sl_description':'','p_sl_account_id':'','p_sl_amount':'','p_sl_drcr':'','p_post':'false'}                            
		if type(params['g_accountId']) == type([]):
			for i in xrange(len(params['g_accountId'])): 
				var_map['p_account_id'] += '"'+params['g_accountId'][i]+'",'
				var_map['p_description'] += '"'+params['g_description'][i]+'",'
				if params['g_DRamount'][i] != '':
					var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_DRamount'][i])+'",'
					var_map['p_drcr'] += '"DR",'                                        
				else:
					var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_CRamount'][i])+'",'
					var_map['p_drcr'] += '"CR",'
			var_map['p_account_id'] = var_map['p_account_id'][:-1]
			var_map['p_description'] = var_map['p_description'][:-1]
			var_map['p_amount'] = var_map['p_amount'][:-1]
			var_map['p_drcr'] = var_map['p_drcr'][:-1]
		else:
			var_map['p_account_id'] += '"'+params['g_accountId']+'"'
			var_map['p_description'] += '"'+params['g_description']+'"'
			if params['DRamount'] != '':
				var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_DRamount'])+'"'
				var_map['p_drcr'] += '"DR"'
			else:
				var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_CRamount'])+'"'
				var_map['p_drcr'] += '"CR"'
				
		var_map['p_subledger'] += params['sl']
		
		if type(params['d_accountId']) == type([]):
			for i in xrange(len(params['d_accountId'])):   
				var_map['p_sl_account_id'] += '"'+params['d_accountId'][i]+'",'
				var_map['p_sl_description'] += '"'+params['d_description'][i]+'",'
				if params['d_DRamount'][i] != '':
					var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_DRamount'][i])+'",'
					var_map['p_sl_drcr'] += '"DR",'
				else:
					var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_CRamount'][i])+'",'
					var_map['p_sl_drcr'] += '"CR",'
			var_map['p_sl_account_id'] = var_map['p_sl_account_id'][:-1]
			var_map['p_sl_description'] = var_map['p_sl_description'][:-1]
			var_map['p_sl_amount'] = var_map['p_sl_amount'][:-1]
			var_map['p_sl_drcr'] = var_map['p_sl_drcr'][:-1]
		else:
			var_map['p_sl_account_id'] += '"'+params['d_accountId']+'"'
			var_map['p_sl_description'] += '"'+params['d_description']+'"'
			if params['d_DRamount'] != '':
				var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_DRamount'])+'"'
				var_map['p_sl_drcr'] += '"DR"'
			else:
				var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_CRamount'])+'"'
				var_map['p_sl_drcr'] += '"CR"'                                      
		#this next line can't possibly work - it should be going to a special function for that subledger
		result = db_tools.exec_action(dbc,'create_journal',[params['date'], var_map['p_description'],var_map['p_account_id'],var_map['p_amount'],var_map['p_drcr'],var_map['p_sl_description'],var_map['p_sl_account_id'],var_map['p_sl_amount'],var_map['p_sl_drcr'],var_map['p_post']])[0]['create_journal']
		conn.commit()
		result_desc = showErr(int(result))
		broadcast_message = result_desc 
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/finance.py/list_sub_post_journal?broadcast_message=%s&amp;sl=%s"%(broadcast_message,params['sl'])) 

def edit_sub_journal_complete( req,**params ):
	"""actaully updates a subledger journal -- redirects to edit subledger journals page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()               
		var_map={'p_journal_id':'null','p_old_d_item_id':'','p_old_g_item_id':'','p_description':'','p_account_id':'','p_amount':'','p_drcr':'','p_subledger':'','p_sl_description':'','p_sl_account_id':'','p_sl_amount':'','p_sl_drcr':'','p_post':'false','p_old_g_amount':'','p_old_g_description':''}
		if params.has_key('old_d_accountId'):
			old_accountset = params['old_d_accountId']
			if type(old_accountset) == type([]):
				for i in xrange(len(params['old_d_accountId'])):
					var_map['p_old_d_item_id'] += params['old_d_accountId'][i]+','
				var_map['p_old_d_item_id'] = var_map['p_old_d_item_id'][:-1]
			else:
				var_map['p_old_d_item_id'] += params['old_d_accountId']
		
		if params.has_key('old_g_accountId'):
			if params['old_g_DRamount']!='':
				var_map['p_old_g_amount'] = utils.escape_comma_in_number(params['old_g_DRamount'])
			else:
				var_map['p_old_g_amount'] = utils.escape_comma_in_number(params['old_g_CRamount'])
			var_map['p_old_g_description'] = params['old_g_description']
			
			old_accountset = params['old_g_accountId']
			if type(old_accountset) == type([]):
				for i in xrange(len(params['old_g_accountId'])):
					var_map['p_old_g_item_id'] += params['old_g_accountId'][i]+','
				var_map['p_old_g_item_id'] = var_map['p_old_g_item_id'][:-1]
			else:
				var_map['p_old_g_item_id'] += params['old_g_accountId']
		var_map['p_journal_id'] = params['j']
		if params.has_key('g_accountId'):
			if type(params['g_accountId']) == type([]):
				for i in xrange(len(params['g_accountId'])): 
					var_map['p_account_id'] += '"'+params['g_accountId'][i]+'",'
					var_map['p_description'] += '"'+params['g_description'][i]+'",'
					if params['g_DRamount'][i] != '':
						var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_DRamount'][i])+'",'
						var_map['p_drcr'] += '"DR",'                                        
					else:
						var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_CRamount'][i])+'",'
						var_map['p_drcr'] += '"CR",'
				var_map['p_account_id'] = var_map['p_account_id'][:-1]
				var_map['p_description'] = var_map['p_description'][:-1]
				var_map['p_amount'] = var_map['p_amount'][:-1]
				var_map['p_drcr'] = var_map['p_drcr'][:-1]
			else:
				var_map['p_account_id'] += '"'+params['g_accountId']+'"'
				var_map['p_description'] += '"'+params['g_description']+'"'
				if params['g_DRamount'] != '':
					var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_DRamount'])+'"'
					var_map['p_drcr'] += '"DR"'
				else:
					var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['g_CRamount'])+'"'
					var_map['p_drcr'] += '"CR"'
		var_map['p_subledger'] = params['sl']
		if params.has_key('d_accountId'):
			if type(params['d_accountId']) == type([]):
				for i in xrange(len(params['d_accountId'])):   
					var_map['p_sl_account_id'] += '"'+params['d_accountId'][i]+'",'
					var_map['p_sl_description'] += '"'+params['d_description'][i]+'",'
					if params['d_DRamount'][i] != '':
						var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_DRamount'][i])+'",'
						var_map['p_sl_drcr'] += '"DR",'
					else:
						var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_CRamount'][i])+'",'
						var_map['p_sl_drcr'] += '"CR",'
				var_map['p_sl_account_id'] = var_map['p_sl_account_id'][:-1]
				var_map['p_sl_description'] = var_map['p_sl_description'][:-1]
				var_map['p_sl_amount'] = var_map['p_sl_amount'][:-1]
				var_map['p_sl_drcr'] = var_map['p_sl_drcr'][:-1]
			else:
				var_map['p_sl_account_id'] += '"'+params['d_accountId']+'"'
				var_map['p_sl_description'] += '"'+params['d_description']+'"'
				if params['d_DRamount'] != '':
					var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_DRamount'])+'"'
					var_map['p_sl_drcr'] += '"DR"'
				else:
					var_map['p_sl_amount'] += '"'+utils.escape_comma_in_number(params['d_CRamount'])+'"'
					var_map['p_sl_drcr'] += '"CR"'                                      
		logger.debug(params['j'])
		result = db_tools.exec_action(dbc,'edit_journal',[var_map['p_journal_id'],var_map['p_old_g_item_id'],var_map['p_old_g_amount'],var_map['p_old_g_description'],var_map['p_old_d_item_id'],var_map['p_description'],var_map['p_account_id'],var_map['p_amount'],var_map['p_drcr'],var_map['p_sl_description'],var_map['p_sl_account_id'],var_map['p_sl_amount'],var_map['p_sl_drcr'],var_map['p_post']])[0]['edit_journal']
		conn.commit()
		result_desc = showErr(int(result))
		broadcast_message = result_desc 
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/finance.py/edit_sub_journal?broadcast_message=%s&amp;sl=%s&amp;j=%s"%(broadcast_message,params['sl'],params['j']))

def post_journal( req,**params ):
	"""actually posts a GL journal -- redirects to finance index page (GL)"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()
		broadcast_message = ''
		result = ''                
		if params.has_key('journalId'):
			if type(params['journalId']) == type([]):                                 
				for journalid in params['journalId']:
					result = db_tools.exec_action(dbc,'post_journal',[journalid])[0]['post_journal'] 
					if result % 100 !=0:
						break                                                           
			else:
				result = db_tools.exec_action(dbc,'post_journal',[params['journalId']])[0]['post_journal']
			conn.commit()
			result_desc = showErr(int(result))
			broadcast_message = result_desc
		else:
			broadcast_message = 'No journals selected'
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py?broadcast_message=%s'%broadcast_message)

def post_sub_journal( req,**params ):
	"""actually posts a subledger journal -- redirects to subledger index page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()
		broadcast_message = ''
		result = '' 
		if params.has_key('journalId'):
			if type(params['journalId']) == type([]):
				for journalid in params['journalId']:
					result = db_tools.exec_action(dbc,'post_journal',[journalid])[0]['post_journal'] 
					if result % 100 !=0:
						break                                                           
			else:
				result = db_tools.exec_action(dbc,'post_journal',[params['journalId']])[0]['post_journal']
			conn.commit()
			result_desc = showErr(int(result))
			broadcast_message = result_desc
		else:
			broadcast_message = 'No journals selected'
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py/sub_account_index?sl=%s&amp;broadcast_message=%s'%(params['sl'],broadcast_message))

def list_post_journal( req,**params ):
	"""displays a list of postable GL journals, with links"""
	page = Page(req, company_db=True)	
	journals = db_tools.exec_action(page.company_db.cursor(),'get_journals',['is NULL'])
	for journal in journals:
		journal['label'] = '<a href="/bin/finance.py/journal_details?j=%s%s">%s\' \'%s</a>'%(str(journal['id']),info.query_string.replace('?', '&amp;'),str(journal['id']),str(journal['date']))
	form_setup={
		'id':'journals',
		'action':'/bin/finance.py/post_journal',
		'fieldsets':[{
			'id':None,
			'classid':'form fin-label',
			'legend':'Completed Journals',
			'items':[ 
				{'type':'checkbox','checkbox_type':2,'checkbox_value':journals,'whole_label':None,'label':'label','id':'id','name':'journalId','value':'id','format':4},                                        
			]
		}],                      
		'template_dir':info.getTemplateDir(req),
		'form_bottom': {'type':'div','id':None,'classid':'bottom input','style':None,'items':[{'type':'submit','id':'submit','name':'submit','value':'Post selected to ledger'}]},
	}
	page.content = {
		'journals':journals,
		'info_query_string':info.query_string.replace('?', '&amp;'),
		'uform':form.user_form(**form_setup)
	}
	page.setup = {
		'breadcrumbs':['finance','Post Journals'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked', 'subledger','spacer', 'journal', 'editjournal', 'postjournal!','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Post Journals',
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or '',
		'template':'finance/fjp.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def list_sub_post_journal( req,**params ):
	"""displays a list of postable subledger journals, with links"""
	page = Page(req, company_db=True)
	dbc=page.company_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	journals = db_tools.exec_action(page.company_db.cursor(),'get_journals',['=\'%s\''%params['sl']])
	ledger = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0]['name']
	page.content = {
		'journals':journals,
		'info_query_string':info.query_string.replace('?', '&amp;'),
		'info_sl':params['sl']
	}
	page.setup = {
		'breadcrumbs':['finance','subledger','Journals'],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal','editsubjournal','postsubjournal!'],
		'content_title':'Unposted Journals',
		'broadcast_message':broadcast_message,
		'template':'finance/fjp-sl.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl']
	}
	return page.render()

def journal_details( req,**params ):
	"""displays a GL journal"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	jdate = db_tools.exec_action(dbc,'get_journal2',[params['j']])[0]['id']
	page.content = {
		'no_render':jtable(dbc, params['j'], info.query_string)
	}
	page.setup = {
		'window_title':'J' + params['j'],
		'breadcrumbs':['finance','Journal #' + params['j']],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked', 'subledger','spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Journal #' + str(jdate),
		'template':'no_render.opt'
	}
	page.info = {
		'query_string':info.query_string,
		'j':params['j']
	}
	return page.render()

def sub_journal_details( req,**params ):
	"""displays a GL journal"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	control = db_tools.exec_action(dbc,'get_coa2',[params['sl']])[0] 
	ledger = control['name']	
	jdate = db_tools.exec_action(dbc,'get_journal2',[params['j']])[0]['id']                
	page.content = {
		'no_render':sljtable(dbc, params['j'], params['sl'], control['id'], info.query_string)
	}
	page.setup = {
		'window_title':'J' + params['j'],
		'breadcrumbs':['finance','subledger','J' + params['j']],
		'menu_title':ledger + ' Ledger',
		'menu':(params['sl']=='ar' or params['sl']=='ap') and ['spacer'] or  ['subaccount','spacer','subjournal','editsubjournal','postsubjournal'],
		'content_title':jdate,
		'template':'no_render.opt'
	}
	page.info = {
		'query_string':info.query_string,
		'ledger':ledger,
		'sl':params['sl'],
		'j':params['j']
	}
	return page.render()

def create_sub_ledger(req, **params):
	"""actually creates a subledger -- redirects to finance index page (GL)"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()
		control_account_id = params['control_id']
		subledger_name = params['short_name']
		import re
		p=re.compile(r'[a-zA-Z]+[a-zA-Z0-9_]*[a-zA-Z0-9]')
		v=p.match(subledger_name)
		if not v:
			broadcast_message="Subledger shortname must start with a letter and consist of only alphanumeric characters"
			util.redirect(req,'/bin/finance.py?broadcast_message=%s'%broadcast_message)
		else:                                               
			result = db_tools.exec_action(dbc,'create_subledger',[subledger_name,control_account_id])[0]['create_subledger']
			conn.commit()                
			result_desc = showErr(int(result))
			broadcast_message = result_desc
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py?broadcast_message=%s'%broadcast_message)                

def insert_sub_ledger(req,**params):
	"""displays a form for creating a subledger"""
	page = Page(req, company_db=True)
	page.content = {
		'candidates':db_tools.exec_action(page.company_db.cursor(),'get_candidates')
	}
	page.setup = {
		'breadcrumbs':['finance','Create a Subsidiary Ledger'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked', 'subledger!','spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'content_title':'Create a Subsidiary Ledger',
		'template':'finance/flc.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def create_journal( req,**params ):
	"""actually creates a GL journal -- redirects to post journals page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()            
		var_map={'p_description':'','p_account_id':'','p_amount':'','p_drcr':'','p_subledger':'','p_sl_description':'','p_sl_account_id':'','p_sl_amount':'','p_sl_drcr':'','p_post':'false'}
		var_map['p_description'] = ''
		var_map['p_account_id'] = ''
		var_map['p_amount'] = ''
		var_map['p_drcr'] = ''
		if type(params['account_id']) == type([]):
			for i in xrange(len(params['account_id'])):
				var_map['p_account_id'] += '"'+params['account_id'][i]+'",'
				var_map['p_description'] += '"'+params['description'][i]+'",'
				if params['dr_amount'][i] != '':
					var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['dr_amount'][i])+'",'
					var_map['p_drcr'] += '"DR",'
				else:
					var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['cr_amount'][i])+'",'
					var_map['p_drcr'] += '"CR",'
			var_map['p_account_id'] = var_map['p_account_id'][:-1]
			var_map['p_description'] = var_map['p_description'][:-1]
			var_map['p_amount'] = var_map['p_amount'][:-1]
			var_map['p_drcr'] = var_map['p_drcr'][:-1]
		else:
			var_map['p_account_id'] += '"'+params['account_id']+'"'
			var_map['p_description'] += '"'+params['description']+'"'
			if params['dr_amount'] != '':
				var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['dr_amount'])+'"'
				var_map['p_drcr'] += '"DR"'
			else:
				var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['cr_amount'])+'"'
				var_map['p_drcr'] += '"CR"'
		result = db_tools.exec_action(dbc,'create_journal',[params['journal_date'],var_map['p_description'],var_map['p_account_id'],var_map['p_amount'],var_map['p_drcr'],var_map['p_sl_description'],var_map['p_sl_account_id'],var_map['p_sl_amount'],var_map['p_sl_drcr'],var_map['p_post']])[0]['create_journal']
		conn.commit()
		result_desc = showErr(int(result))
		broadcast_message = result_desc                
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py/list_post_journal?broadcast_message=%s'%broadcast_message)

def edit_journal_complete( req,**params ):
	"""actually updates a GL journal -- redirects to post journals page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()            
		broadcast_message = ''
		var_map={'p_journal_id':'null','p_old_d_item_id':'','p_old_g_item_id':'','p_description':'','p_account_id':'','p_amount':'','p_drcr':'','p_subledger':'','p_sl_description':'','p_sl_account_id':'','p_sl_amount':'','p_sl_drcr':'','p_post':'false','p_old_g_amount':'null','p_old_g_description':''}
		var_map['p_description'] = ''
		var_map['p_account_id'] = ''
		var_map['p_amount'] = ''
		var_map['p_drcr'] = ''
		if params.has_key('old_accountId'):
			old_accountset = params['old_accountId']
			if type(old_accountset) == type([]):
				for i in xrange(len(params['old_accountId'])):
					var_map['p_old_g_item_id'] += params['old_accountId'][i]+','
				var_map['p_old_g_item_id'] = var_map['p_old_g_item_id'][:-1]
			else:
				var_map['p_old_g_item_id'] += params['old_accountId']
		var_map['p_journal_id'] = params['j']
		if params.has_key('accountId'):
			if type(params['accountId']) == type([]):
				for i in xrange(len(params['accountId'])):
					var_map['p_account_id'] += '"'+params['accountId'][i]+'",'
					var_map['p_description'] += '"'+params['description'][i]+'",'
					if params['DRamount'][i] != '':
						var_map['p_amount'] += '"'+params['DRamount'][i]+'",'
						var_map['p_drcr'] += '"DR",'
					else:
						var_map['p_amount'] += '"'+params['CRamount'][i]+'",'
						var_map['p_drcr'] += '"CR",'
				var_map['p_account_id'] = var_map['p_account_id'][:-1]
				var_map['p_description'] = var_map['p_description'][:-1]
				var_map['p_amount'] = var_map['p_amount'][:-1]
				var_map['p_drcr'] = var_map['p_drcr'][:-1]
			else:
				var_map['p_account_id'] += '"'+params['accountId']+'"'
				var_map['p_description'] += '"'+params['description']+'"'
				if params['DRamount'] != '':
					var_map['p_amount'] += '"'+params['DRamount']+'"'
					var_map['p_drcr'] += '"DR"'
				else:
					var_map['p_amount'] += '"'+params['CRamount']+'"'
					var_map['p_drcr'] += '"CR"'
		result = db_tools.exec_action(dbc,'edit_journal',[var_map['p_journal_id'],var_map['p_old_g_item_id'],var_map['p_old_g_amount'],var_map['p_old_g_description'],var_map['p_old_d_item_id'],var_map['p_description'],var_map['p_account_id'],var_map['p_amount'],var_map['p_drcr'],var_map['p_subledger'],var_map['p_sl_description'],var_map['p_sl_account_id'],var_map['p_sl_amount'],var_map['p_sl_drcr'],var_map['p_post']])[0]['edit_journal']
		conn.commit()
		result_desc = showErr(int(result))
		broadcast_message = result_desc
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py/list_post_journal?broadcast_message=%s'%(params['j'],broadcast_message))

def delete_journal( req,**params ):
	"""actually deletes a GL journal -- redirects to journal list page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()            
		broadcast_message = ''
		if params.has_key('journalId'):
			journalset = params['journalId']
			if type(journalset)==type([]):
				for journal_id in journalset:
					db_tools.exec_action(dbc,'delete_journal',[journal_id])
			else:
				db_tools.exec_action(dbc,'delete_journal',[journalset])
			conn.commit()
			broadcast_message = 'Deleted'
		else:
			broadcast_message = 'No journal selected!'
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/finance.py/list_journal?broadcast_message=%s"%broadcast_message) 

def delete_sub_journal( req,**params ):
	"""actually deletes a subledger journal -- redirects to journal list page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()            
		broadcast_message = ''
		if params.has_key('journalId'):
			journalset = params['journalId']
			if type(journalset)==type([]):
				for journal_id in journalset:
					db_tools.exec_action(dbc,'delete_journal',[journal_id])
			else:
				db_tools.exec_action(dbc,'delete_journal',[journalset])
			conn.commit()
			broadcast_message = 'Deleted'
		else:
			broadcast_message = 'No journal selected!'
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/finance.py/list_sub_journal?broadcast_message=%s&amp;sl=%s"%(broadcast_message,params['sl'])) 

def insert_expenses( req,**params ):
	"""displays a form for recording expenses"""
	import datetime
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	all_expense_taxes = db_tools.exec_action(dbc, 'get_same_type_taxes', ['retail purchase'])
	page.content = {
		'expense_accounts':db_tools.exec_action(dbc,'get_same_type_accounts', ['EXPENSE']),
		'skin':page.session['skin'],
		'today':datetime.date.today().isoformat(),
		'all_expense_taxes':[],
		'last_expense_taxes':[]
	}
	if len(all_expense_taxes)>0:
		for i in range(len(all_expense_taxes)-1):
			page.content['all_expense_taxes'].append(all_expense_taxes[i])
		page.content['last_expense_taxes'].append(all_expense_taxes[-1])		
	page.setup = {
		'breadcrumbs':['finance','Record Expenses'],
		'menu_title':'General Ledger',
		'menu':['recordexp!', 'spacer', 'account', 'special','linked','subledger', 'spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or None,
		'content_title':'Record Expenses',
		'template':'finance/fer.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def create_expenses_report( req,**params ):
	"""actually creates an expense report -- redirects to expense report form"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()            
		all_expense_taxes = db_tools.exec_action(dbc, 'get_same_type_taxes', ['retail purchase'])
		#TODO:add string escape (",)  and  add a function to translate None to NULL.
		var_map = {'p_report_id':'', 'p_expenses_relate_to':'','p_account_id':'','p_amount':'','p_description':'','p_tax_ids':'', 'p_tax_amounts':''}
		var_map['p_expenses_relate_to'] = utils.escape_quotes_in_sql(params['relate_to'])
		if type(params['accountId']) == list:
			for i in xrange(len(params['accountId'])):
				var_map['p_account_id'] += '"'+params['accountId'][i]+'",'
				var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['DRamount'][i])+'",'
				var_map['p_description'] += '"'+utils.escape_quotes_in_procedure(params['description'][i])+'",'
				tax_id_temp, tax_amount_temp = '', ''
				for tax in all_expense_taxes:
					tax_id_temp += str(tax['id']) +','
					tax_amount_temp += utils.escape_comma_in_number(params[tax['shortname']][i]) + ','
				tax_id_temp = '"'+tax_id_temp[:-1]+'"'
				tax_amount_temp = '"'+tax_amount_temp[:-1]+'"'
				var_map['p_tax_ids'] += tax_id_temp + ','
				var_map['p_tax_amounts'] += tax_amount_temp + ','
			var_map['p_account_id'] = var_map['p_account_id'][:-1]
			var_map['p_amount'] = var_map['p_amount'][:-1]
			var_map['p_description'] = var_map['p_description'][:-1]
			var_map['p_tax_ids'] = '{'+var_map['p_tax_ids'][:-1]+'}'
			var_map['p_tax_amounts'] = '{'+var_map['p_tax_amounts'][:-1]+'}'
		else:
			var_map['p_account_id'] += '"'+params['accountId']+'"'
			var_map['p_amount'] += '"'+utils.escape_comma_in_number(params['DRamount'])+'"'
			var_map['p_description'] += '"'+utils.escape_quotes_in_procedure(params['description'])+'"'
			tax_id_temp, tax_amount_temp = '', ''
			for tax in all_expense_taxes:
				tax_id_temp += str(tax['id']) +','
				tax_amount_temp += utils.escape_comma_in_number(params[tax['shortname']]) + ','
			var_map['p_tax_ids'] = '{"'+tax_id_temp[:-1]+'"}'
			var_map['p_tax_amounts'] = '{"'+tax_amount_temp[:-1]+'"}'
		if len(all_expense_taxes) == 0:
			result = db_tools.exec_action(dbc, 'create_expense_report', [var_map['p_report_id'],params['expense_report_date'],var_map['p_expenses_relate_to'],var_map['p_account_id'], var_map['p_amount'], var_map['p_description'], 'None', 'None'], nullify = True, escaped = True)[0]['create_expense_report']
		else:
			result = db_tools.exec_action(dbc,'create_expense_report', [var_map['p_report_id'],var_map['p_expenses_relate_to'],params['expense_report_date'],var_map['p_account_id'], var_map['p_amount'], var_map['p_description'], var_map['p_tax_ids'], var_map['p_tax_amounts']], nullify = True, escaped = True)[0]['create_expense_report']
		conn.commit()
		broadcast_message = showErr(int(result))
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py/insert_expenses?broadcast_message=%s'% broadcast_message)

def special_params_setting_page(req,**params):
	"""displays a form for setting special account flags"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	set_params,leave_params = check_special_params(dbc)
	special_params = [{'param_id':'special_ac_cash','param_name':'CASH','param_desc':'CASH'},{'param_id':'special_ac_equip','param_name':'Capital Equipment','param_desc':'Capital Equipment'},{'param_id':'special_ac_comm_pay','param_name':'Commissions Payable','param_desc':'Commissions Payable'},{'param_id':'special_ac_sales_rev','param_name':'Sales Revenue','param_desc':'Sales Revenue'},{'param_id':'special_ac_comm_exp','param_name':'Commissions','param_desc':'Commissions'}]
	special_params_details = db_tools.exec_action(dbc, 'get_special_params_details', [tuple([a['param_id'] for a in special_params])])
	revenueAccounts = db_tools.exec_action(dbc,'get_accounts_by_type_without_sub',['REVENUE'])
	expenseAccounts = db_tools.exec_action(dbc,'get_accounts_by_type_without_sub',['EXPENSE'])
	liabilityAccounts = db_tools.exec_action(dbc,'get_accounts_by_type_without_sub',['LIABILITY'])
	assetAccounts = db_tools.exec_action(dbc,'get_accounts_by_type_without_sub',['ASSET'])
	for param in special_params:
		if param['param_id']=='special_ac_cash':
			flag=False
			if assetAccounts:
				for asset in assetAccounts:
					if special_params_details:
						for specialParams in special_params_details:
							if specialParams['code_id']==param['param_id'] and specialParams['code_value']==asset['id']:
								specialParams['seted']=True
								flag=True
								break					
				if not flag:
					for asset in assetAccounts:
						if 'cash' in asset['name'].lower():
							specialParams={}
							specialParams['code_id'] = param['param_id']
							specialParams['code_value'] = asset['id']
							specialParams['seted'] = False
							special_params_details.append(specialParams)
							break
		if param['param_id']=='special_ac_equip':
			flag=False
			if assetAccounts:
				for asset in assetAccounts:
					if special_params_details:
						for specialParams in special_params_details:
							if specialParams['code_id']==param['param_id'] and specialParams['code_value']==asset['id']:
								specialParams['seted']=True
								flag=True
								break
				if not flag:
					for asset in assetAccounts:
						if 'equipment' in asset['name'].lower():
							specialParams={}
							specialParams['code_id'] = param['param_id']
							specialParams['code_value'] = asset['id']
							specialParams['seted'] = False
							special_params_details.append(specialParams)
							break
		if param['param_id']=='special_ac_comm_pay':
			flag=False
			if liabilityAccounts:
				for liability in liabilityAccounts:
					if special_params_details:
						for specialParams in special_params_details:
							if specialParams['code_id']==param['param_id'] and specialParams['code_value']==liability['id']:
								specialParams['seted']=True
								flag=True
								break
				if not flag:
					for liability in liabilityAccounts:
						if 'commission' in liability['name'].lower():
							specialParams={}
							specialParams['code_id'] = param['param_id']
							specialParams['code_value'] = liability['id']
							specialParams['seted'] = False
							special_params_details.append(specialParams)
							break
							
		if param['param_id']=='special_ac_sales_rev':
			flag=False
			if revenueAccounts:
				for rev in revenueAccounts:
					if special_params_details:
						for specialParams in special_params_details:
							if specialParams['code_id']==param['param_id'] and specialParams['code_value']==rev['id']:
								specialParams['seted']=True
								flag=True
								break
				if not flag:
					for rev in revenueAccounts:
						if 'sales' in rev['name'].lower():
							specialParams={}
							specialParams['code_id'] = param['param_id']
							specialParams['code_value'] = rev['id']
							specialParams['seted'] = False
							special_params_details.append(specialParams)
							break
							
		if param['param_id']=='special_ac_comm_exp':
			flag=False
			if expenseAccounts:
				for exp in expenseAccounts:
					if special_params_details:
						for specialParams in special_params_details:
							if specialParams['code_id']==param['param_id'] and specialParams['code_value']==exp['id']:
								specialParams['seted']=True
								flag=True
								break
				if not flag:
					for exp in expenseAccounts:
						if 'commission' in exp['name'].lower():
							specialParams={}
							specialParams['code_id'] = param['param_id']
							specialParams['code_value'] = exp['id']
							specialParams['seted'] = False
							special_params_details.append(specialParams)
							break
	#return special_params_details,len(special_params_details)
	page.content = {
		'set_params':set_params,
		'leave_params':leave_params,
		'special_params':special_params,
		'special_params_details':special_params_details,
		'revenueAccounts':revenueAccounts,
		'expenseAccounts':expenseAccounts,
		'liabilityAccounts':liabilityAccounts,
		'assetAccounts':assetAccounts,
		#'redirect_url':'/bin/'+page.session['user_access'].split(',')[0]+'.py'
		'redirect_url':'/bin/'+page.session['user_access'].values()[0]+'.py'
	}
	page.setup = {
		'breadcrumbs':['finance','Set Special Accounts'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special!','linked','subledger', 'spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or None,
		'content_title':'Set Special Accounts',
		'template':'finance/special_params_setting.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def special_params_setting_submit(req,**params):
	"""actually sets the special account flags -- redirects to special accounts form"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()
		special_params = [{'param_id':'special_ac_cash','param_name':'CASH','param_desc':'CASH'},{'param_id':'special_ac_equip','param_name':'Capital Equipment','param_desc':'Capital Equipment'},{'param_id':'special_ac_comm_pay','param_name':'Commissions Payable','param_desc':'Commissions Payable'},{'param_id':'special_ac_sales_rev','param_name':'Sales Revenue','param_desc':'Sales Revenue'},{'param_id':'special_ac_comm_exp','param_name':'Commissions','param_desc':'Commissions'}]
		for param in special_params:
			if params.has_key(param['param_id']):	
				logger.debug('***** exist Code  *******')
				logger.debug(param['param_desc'])
				existCode = db_tools.exec_action(dbc, 'check_sys_code', [param['param_id'],param['param_desc']])
				if params[param['param_id']].strip():
					if existCode:
						db_tools.exec_action(dbc, 'update_sys_code', [params[param['param_id']],param['param_id'],param['param_desc']])
					else:
						db_tools.exec_action(dbc, 'insert_sys_code', [param['param_id'],params[param['param_id']],param['param_desc']])
				else:
					if existCode:
						db_tools.exec_action(dbc, 'delete_sys_code', [param['param_id'],param['param_desc']])
		conn.commit()
		broadcast_message = 'Special accounts have been flagged'
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/finance.py/special_params_setting_page")

def linked_account_setting_page( req,**params ):
	"""displays a form for setting linked accounts (for transaction taxes)"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	taxSets = db_tools.exec_action(dbc,'get_tax')
	liabilityAccounts = db_tools.exec_action(dbc,'get_accounts_by_type_without_sub',['LIABILITY'])
	assetAccounts = db_tools.exec_action(dbc,'get_accounts_by_type_without_sub',['ASSET'])
	linkedAccountDetails = db_tools.exec_action(dbc,'get_linked_account_details')
	for tax in taxSets:
		flag=False
		if liabilityAccounts:
			for liability in liabilityAccounts:
				for linkedAccount in linkedAccountDetails:
					if linkedAccount['transaction_tax_shortname']==tax['shortname'] and linkedAccount['account_id']==liability['id']:
						linkedAccount['seted'] = True
						flag=True
						break
			if not flag:
				for liability in liabilityAccounts:
					if tax['shortname'].lower() in liability['name'].lower():
						linkedAccount={}
						linkedAccount['transaction_tax_shortname']=tax['shortname']
						linkedAccount['account_id']=liability['id']
						linkedAccount['seted']=False
						linkedAccountDetails.append(linkedAccount)
						flag=True
						break
			if not flag:
				for liability in liabilityAccounts:
					if 'tax' in liability['name'].lower():
						linkedAccount={}
						linkedAccount['transaction_tax_shortname']=tax['shortname']
						linkedAccount['account_id']=liability['id']
						linkedAccount['seted']=False
						linkedAccountDetails.append(linkedAccount)				
						break
		if tax['track_on_purchases']:
			flag=False
			if assetAccounts:
				for asset in assetAccounts:
					for linkedAccount in linkedAccountDetails:
						if linkedAccount['transaction_tax_shortname']==tax['shortname'] and linkedAccount['account_id']==asset['id']:
							linkedAccount['seted'] = True
							flag=True
							break
				if not flag:
					for asset in assetAccounts:						
						if tax['shortname'].lower() in asset['name'].lower():
							linkedAccount={}
							linkedAccount['transaction_tax_shortname']=tax['shortname']
							linkedAccount['account_id']=asset['id']
							linkedAccount['seted']=False
							linkedAccountDetails.append(linkedAccount)
							flag=True
							break
				if not flag:
					for asset in assetAccounts:						
						if 'tax' in asset['name'].lower():
							linkedAccount={}
							linkedAccount['transaction_tax_shortname']=tax['shortname']
							linkedAccount['account_id']=asset['id']
							linkedAccount['seted']=False
							linkedAccountDetails.append(linkedAccount)	
							break
	page.content = {
		'taxSets':taxSets,
		'liabilityAccounts':liabilityAccounts,
		'assetAccounts':assetAccounts,
		'linkedAccountDetails':linkedAccountDetails		
	}
	page.setup = {
		'breadcrumbs':['finance','Set Tax Accounts'],
		'menu_title':'General Ledger',
		'menu':['recordexp', 'spacer', 'account', 'special','linked!','subledger', 'spacer', 'journal', 'editjournal', 'postjournal','spacer', 'balsheet','pl','taxreport'],
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or None,
		'content_title':'Set Tax Accounts',
		'template':'finance/olas.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def linked_account_setting_submit(req,**params):
	"""actually sets the linked accounts -- redirects to linked accounts form"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()
		RETAIL_SALE = {'suffix':'_retail_sale','value':'retail sale'}
		RETAIL_PURCHASE = {'suffix':'_retail_purchase','value':'retail purchase'}
		taxSets = db_tools.exec_action(dbc,'get_tax')
		for tax in taxSets:
			if params.has_key(tax['shortname']+RETAIL_SALE['suffix']):
				existLinkedAccount = db_tools.exec_action(dbc,'get_linked_account_of_tax',[tax['shortname'],RETAIL_SALE['value']])
				if params[tax['shortname']+RETAIL_SALE['suffix']].strip():
					if existLinkedAccount:
						db_tools.exec_action(dbc,'update_linked_account',[params[tax['shortname']+RETAIL_SALE['suffix']],tax['shortname'],RETAIL_SALE['value']])
					else:
						db_tools.exec_action(dbc,'insert_linked_account',[tax['shortname'],RETAIL_SALE['value'],params[tax['shortname']+RETAIL_SALE['suffix']]])
				else:
					if existLinkedAccount:
						db_tools.exec_action(dbc,'delete_linked_account',[tax['shortname'],RETAIL_SALE['value']])
			if params.has_key(tax['shortname']+RETAIL_PURCHASE['suffix']):
				existLinkedAccount = db_tools.exec_action(dbc,'get_linked_account_of_tax',[tax['shortname'],RETAIL_PURCHASE['value']])
				if params[tax['shortname']+RETAIL_PURCHASE['suffix']].strip():
					if existLinkedAccount:
						db_tools.exec_action(dbc,'update_linked_account',[params[tax['shortname']+RETAIL_PURCHASE['suffix']],tax['shortname'],RETAIL_PURCHASE['value']])
					else:
						db_tools.exec_action(dbc,'insert_linked_account',[tax['shortname'],RETAIL_PURCHASE['value'],params[tax['shortname']+RETAIL_PURCHASE['suffix']]])
				else:
					if existLinkedAccount:
						db_tools.exec_action(dbc,'delete_linked_account',[tax['shortname'],RETAIL_PURCHASE['value']])
		conn.commit()
		broadcast_message = 'Linked accounts set'
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,'/bin/finance.py/linked_account_setting_page?broadcast_message=%s'%broadcast_message)	
